import cx_Oracle
import json
import http.client
import os
from datetime import *

os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.AL32UTF8'
TOKEN = 'buptcampuscard'
ORACLE_URL = 'lzc/123456@10.3.55.52/ywkdb'
TARGET_URL = '115.159.211.31'
MAX_LEN = 1000000


class JsonEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, datetime):
            return obj.strftime('%Y-%m-%d %H:%M:%S')
        elif isinstance(obj, date):
            return obj.strftime('%Y-%m-%d')
        else:
            return json.JSONEncoder.default(self, obj)


def post(url, js_str):
    conn = http.client.HTTPConnection(TARGET_URL)
    conn.request(method='POST', url=url, body=js_str)
    response = conn.getresponse()
    conn.close()
    return response.status, response.reason


def js_list(SQL, init):
    conn = cx_Oracle.connect(ORACLE_URL)
    cursor = conn.cursor()
    cursor.execute(SQL)
    first = cursor.fetchone()
    js_len = len(json.dumps(dict(zip(init['KEY'], first)), cls=JsonEncoder))
    max = int(MAX_LEN / js_len)
    js_list = []
    print('每条数据长度:' + str(js_len))
    print('每次最多发送' + str(max) + '条数据')
    cursor.scroll(mode="first")
    print('装载发送数据...')
    while True:
        re = cursor.fetchmany(numRows=max)
        R = []
        if len(re) == 0:
            break
        for each in re:
            R.append(dict(zip(init['KEY'], each)))
        js = {init['TABLE_NAME']: R, 'TOKEN': TOKEN}
        js_list.append(json.dumps(js, cls=JsonEncoder))
    print('装载完成，开始同步.......')
    cursor.close()
    conn.close()

    return js_list


def syn_course():
    SQL = 'SELECT DISTINCT COURSE_CODE,COURSE_NAME FROM YDZC.VIEW_STUDENT_GRADE  ORDER BY COURSE_CODE'
    js_init = {
        'TABLE_NAME': 'COURSE_INFO',
        'KEY': ['COURSE_ID', 'COURSE_NAME']
    }
    print('正在从本地库获取课程数据.......')
    list = js_list(SQL, js_init)
    flag = 1
    total = len(list)
    for each in list:
        print(str(post(url='/syn/course/', js_str=each)) + '[%d / %d]' % (flag, total))
        flag += 1
    return '同步完成'


def syn_student():
    SQL = "SELECT STUDENT_NO,STUDENT_NAME FROM YDZC.VIEW_STUDENT_BASICINFO WHERE IS_IN_SCHOOL='1'"
    js_init = {
        'TABLE_NAME': 'STUDENT_INFO',
        'KEY': ['STUDENT_ID', 'STUDENT_NAME']
    }
    print('正在从本地库获取学生数据.......')
    list = js_list(SQL, js_init)
    flag = 1
    total = len(list)
    for each in list:
        print(str(post(url='/syn/student/', js_str=each)) + '[%d / %d]' % (flag, total))
        flag += 1
    return '同步完成'


def syn_news():
    SQL = "SELECT * FROM YDZC.VIEW_NEWS WHERE NEWS_PUB_DATE >= SYSDATE - 10"
    js_init = {
        'TABLE_NAME': 'NEWS',
        'KEY': ['NEWS_ID',
                'NEWS_TITLE',
                'NEWS_AUTHOR',
                'NEWS_CATEGORY',
                'NEWS_PUB_DATE',
                'NEWS_CONTENT',
                ]
    }
    print('正在从本地库获取新闻数据.......')
    list = js_list(SQL, js_init)
    flag = 1
    total = len(list)
    for each in list:
        print(str(post(url='/syn/news/', js_str=each)) + '[%d / %d]' % (flag, total))
        flag += 1
    return '同步完成'


def syn_book():
    SQL = "SELECT * FROM YDZC.VIEW_BOOK_SEARCH"
    js_init = {
        'TABLE_NAME': 'BOOK_INFO',
        'KEY': ['name',
                'isbn',
                'id',
                'author',
                'publish',
                'index',
                ]
    }
    print('正在从本地库获取图书信息数据.......')
    list = js_list(SQL, js_init)
    flag = 1
    total = len(list)
    for each in list:
        print(str(post(url='/syn/book/', js_str=each)) + '[%d / %d]' % (flag, total))
        flag += 1
    return '同步完成'


def syn_save():
    SQL = "SELECT OUTID,OPDT,DSCRP,OPFARE,ODDFARE " \
          "FROM YDZC.V_YKT_SAVE JOIN YDZC.VIEW_STUDENT_BASICINFO " \
          "ON OUTID = STUDENT_NO " \
          "WHERE IS_IN_SCHOOL = '1'"
    js_init = {
        'TABLE_NAME': 'SAVE_RECORD',
        'KEY': ['student_id',
                'datetime',
                'way',
                'amount',
                'balance',
                ]
    }
    print('正在从本地库获取一卡通圈存数据.......')
    list = js_list(SQL, js_init)
    flag = 1
    total = len(list)
    for each in list:
        print(str(post(url='/syn/ecard_record/', js_str=each)) + '[%d / %d]' % (flag, total))
        flag += 1
    return '同步完成'


def syn_consume():
    SQL = "SELECT OUTID,OPDT,DSCRP,OPFARE,ODDFARE " \
          "FROM YDZC.V_YKT_CONSUME JOIN YDZC.VIEW_STUDENT_BASICINFO " \
          "ON OUTID = STUDENT_NO " \
          "WHERE IS_IN_SCHOOL = '1'"
    js_init = {
        'TABLE_NAME': 'CONSUME_RECORD',
        'KEY': ['student_id',
                'datetime',
                'way',
                'amount',
                'balance',
                ]
    }
    print('正在从本地库获取一卡通消费数据.......')
    list = js_list(SQL, js_init)
    flag = 1
    total = len(list)
    for each in list:
        print(str(post(url='/syn/ecard_record', js_str=each)) + '[%d / %d]' % (flag, total))
        flag += 1
    return '同步完成'


def syn_book_lend():
    SQL = "SELECT LEND_TIME,LEND_DEADLINE,BOOK_NAME,READER_ID " \
          "FROM YDZC.VIEW_BOOK_LEND JOIN YDZC.VIEW_STUDENT_BASICINFO " \
          "ON STUDENT_NO = READER_ID " \
          "WHERE IS_IN_SCHOOL = '1'"
    js_init = {
        'TABLE_NAME': 'BOOK_LEND',
        'KEY': ['lend_time',
                'lend_deadline',
                'book_name',
                'student_id',
                ]
    }
    print('正在从本地库获取借书数据.......')
    list = js_list(SQL, js_init)
    flag = 1
    total = len(list)
    for each in list:
        print(str(post(url='/syn/book_lend/', js_str=each)) + '[%d / %d]' % (flag, total))
        flag += 1
    return '同步完成'


def syn_course_selection():
    SQL = "SELECT COURSE_CODE,ID,YEAR,TERM,GRADE " \
          "FROM YDZC.VIEW_STUDENT_GRADE JOIN YDZC.VIEW_STUDENT_BASICINFO " \
          "ON ID = STUDENT_NO " \
          "WHERE IS_IN_SCHOOL = '1'"
    js_init = {
        'TABLE_NAME': 'COURSE_SELECTION',
        'KEY': ['course_id',
                'student_id',
                'year',
                'term',
                'grade',
                ]
    }
    print('正在从本地库获取学生成绩数据.......')
    list = js_list(SQL, js_init)
    flag = 1
    total = len(list)
    for each in list:
        print(str(post(url='/syn/course_selection/', js_str=each)) + '[%d / %d]' % (flag, total))
        flag += 1
    return '同步完成'


print(syn_course_selection())